package sapling.dao;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import sapling.model.Admin;
import sapling.model.Company;
import sapling.model.LandMass;

import java.util.List;

/**
 * Created by ever on 2016/10/9.
 */

@Transactional
public interface LandMassDao extends JpaRepository<LandMass, Long> {

    @Query(value = "SELECT * FROM land_mass WHERE id IN " +
            "( SELECT land_mass_id FROM tree_well WHERE tree_id IN " +
            "( SELECT id FROM tree WHERE tree_species LIKE ?1 ) )",nativeQuery = true)
    List<LandMass> landMass(String tree_species);


    @Query(value = "SELECT * FROM land_mass WHERE id IN " +
            "( SELECT land_mass_id FROM tree_well WHERE tree_id IN " +
            "( SELECT id FROM tree WHERE clump =?1 ) )",nativeQuery = true)
    List<LandMass> landMassClump(int clump);


    @Query(value = "SELECT * FROM land_mass WHERE id IN " +
            "( SELECT land_mass_id FROM tree_well WHERE tree_id IN " +
            "( SELECT id FROM tree WHERE tree_species NOT LIKE ?1 ) )",nativeQuery = true)
    List<LandMass> notLandMass(String tree_species);


    @Query(value = "SELECT  MAX(serial_number) FROM land_mass where area_location_id=?1",nativeQuery = true)
    Long findMaxserialNumber(Long areaLocation_id);

    @Query(value = "SELECT * FROM land_mass WHERE id IN " +
            "( SELECT land_mass_id FROM tree_well WHERE tree_id is null )",nativeQuery = true)
    List<LandMass> nullLandMass();

    List<LandMass> findByGenerate(int i);

    @Query(value = "SELECT * FROM land_mass where area_location_id=?1",nativeQuery = true)
    List<LandMass> findArea(long areaid);

    @Query(value = "SELECT  * FROM land_mass where area_location_id=?1 and serial_number=?2 LIMIT 0,1",nativeQuery = true)
    LandMass findSerialNumber(Long areaLocation_id,String serial_number);

    @Query(value = "SELECT  * FROM land_mass where area_location_id=?1 and xcoordinate=?2",nativeQuery = true)
    List<LandMass> findcolumn(Long areaLocation_id,long x);

    @Query(value = "SELECT  count(*) FROM land_mass where area_location_id=?1",nativeQuery = true)
    int getAreacount(long areaid);

    @Query(value = "SELECT * from land_mass where generate=?1 and area_location_id=?2",nativeQuery = true)
    List<LandMass> findbyGenerateandarea(int generate, long areaid);

    @Query(value = "SELECT * from land_mass where Ycoordinate=(SELECT MAX(Ycoordinate) from land_mass where area_location_id=?1) and area_location_id=?2",nativeQuery = true)
    LandMass findMaxlength(long areaid,long id);

    @Query(value = "SELECT MAX(Xcoordinate) from land_mass where Ycoordinate=?1 and id=?2",nativeQuery = true)
    double findMaxbreadth(double Ycoordinate,long id);

    @Query(value = "SELECT * FROM land_mass WHERE area_location_id=?1 ORDER BY id desc LIMIT 0,1",nativeQuery = true)
    LandMass findMaxSer(long areaid);

    @Query(value = "SELECT * FROM land_mass WHERE area_location_id=?1 ORDER BY (ycoordinate+length) desc LIMIT 0,1",nativeQuery = true)
    LandMass findMaxYlandmass(long areaid);

    @Query(value = "select tree_species from tree where landmassid=?1 group by tree_species",nativeQuery = true)
    List<String> getAlltreename(Long landmassid);

    @Query(value = "select * from land_mass where remarks like %?1%",nativeQuery = true)
    List<LandMass> findRemarks(String remarks);
}
